package com.ibm.dao;

import com.ibm.entity.Brand;
import com.ibm.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class BrandDAO {

    /**
     * 根据分类ID查询品牌列表
     * @param categoryId
     * @return
     */
    public List<Brand> selectBrandsByCategoryId(int categoryId){
        List<Brand> brandList = new ArrayList<>();
        try {
            String sql = "select b.product_id brandId,b.product_name brandName,b.product_image brandLogo,b.product_price brandPrice,b.create_time createTime,b.product_status brandStatus from category_product cb INNER JOIN products b ON cb.fk_product_id = b.product_id where cb.fk_category_id=?";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            brandList = queryRunner.query(sql,new BeanListHandler<Brand>(Brand.class),categoryId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return brandList;
    }

    /**
     * 保存品牌信息
     * @param brand
     * @return 返回生成的品牌的ID
     */
    public int insertBrand(Brand brand){
        int brandId = 0;
        try {
            String sql = "insert into products(product_name,product_image,create_time,product_status) values(?,?,?,?,?)";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            BigInteger object = queryRunner.insert(sql,new ScalarHandler<>(),brand.getBrandName(),brand.getBrandLogo()
                    ,brand.getCreateTime(),brand.getBrandStatus());
            brandId = object.intValue();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return brandId;
    }

    /**
     * 保存分类与品牌的关联关系
     * @param cid
     * @param bid
     * @return
     */
    public int insertCategoryAndBrand(int cid,int bid){
        int i = 0;
        try {
            String sql = "insert into category_product(fk_category_id,fk_product_id) values(?,?)";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            i = queryRunner.update(sql,cid,bid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 根据id查询品牌信息
     * @param brandId
     * @return
     */
    public Brand selectBrandByBrandId(int brandId){
        Brand brand = null;
        try {
            String sql = "select product_id brandId,product_name brandName,product_image brandLogo,product_price brandPrice,create_time createTime,product_status brandStatus from products where product_id=?";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            brand = queryRunner.query(sql,new BeanHandler<Brand>(Brand.class),brandId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return brand;
    }

    /**
     * 修改品牌信息
     * @param brand
     * @return
     */
    public int updateBrand(Brand brand){
        int i = 0;
        try {
            String sql = "update products set product_name=?,product_image=?,product_price=?,create_time=?,product_status=? where product_id=?";
            Object[] params = {brand.getBrandName(),brand.getBrandLogo(),brand.getBrandPrice(),brand.getCreateTime(),brand.getBrandStatus(),brand.getBrandId()};
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            i = queryRunner.update(sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 删除品牌与分类的关联
     * @param brandId
     * @return
     */
    public int deleteCategoryAndBrand(int brandId){
        int i = 0;
        try {
            String sql = "delete from category_product where fk_product_id=?";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            i = queryRunner.update(sql,brandId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 根据品牌ID删除品牌信息
     * @param brandId
     * @return
     */
    public int deleteBrand(int brandId){
        int i = 0;
        try {
            String sql = "delete from products where product_id=?";
            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            i = queryRunner.update(sql,brandId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

}
